﻿CREATE FUNCTION WaterFall(
	@WaterFallID int,
	@Payment money,
	@NewPayment money,
	@ToInterest money,
	@LateFee money,
	@NSF money,
	@EscrowInterest money,
	@EscrowLateFee money,
	@EscrowNSF money
)RETURNS @T TABLE(--ToPrincipal money,
	ToOwnedInterest money,InterestPart money,AddEscrowInterest money,
	PrincipalPart money,
	ToOwnedLateFee money,LateFeePart money, AddEscrowLateFee money,
	ToOwnedNSF money,NSFPart money, AddEscrowNSF money,
	TranBalance money)
BEGIN

IF @WaterFallID = 2 BEGIN -- ALL to principal
	INSERT INTO @T
	SELECT 
	0 ToOwnedInterest ,0 InterestPart ,0 AddEscrowInterest,
	@NewPayment PrincipalPart,
	0 ToOwnedLateFee ,0 LateFeePart ,0 AddEscrowLateFee,
	0 ToOwnedNSF ,0 NSFPart ,0 AddEscrowNSF,
	0 TranBalance 
	RETURN
END

DECLARE @TranBalance money SET @TranBalance = @NewPayment
DECLARE @ToPrincipal money SET @ToPrincipal = dbo.MaxNum(0,@Payment - @ToInterest)

DECLARE @ToOwnedInterest money SET @ToOwnedInterest = dbo.WholeOrLess(@TranBalance,-@EscrowInterest)
SET @TranBalance = @TranBalance - @ToOwnedInterest

DECLARE @InterestPart money SET @InterestPart = dbo.WholeOrLess(@TranBalance,@ToInterest)
SET @TranBalance = @TranBalance - @InterestPart
DECLARE @AddEscrowInterest money SET @AddEscrowInterest = @InterestPart - @ToInterest

DECLARE @PrincipalPart money SET @PrincipalPart = dbo.WholeOrLess(@TranBalance,@ToPrincipal)
SET @TranBalance = @TranBalance - @PrincipalPart

DECLARE @ToOwnedLateFee money SET @ToOwnedLateFee = dbo.WholeOrLess(@TranBalance,-@EscrowLateFee)
IF @ToOwnedLateFee < 1 SET @ToOwnedLateFee = 0
SET @TranBalance = @TranBalance - @ToOwnedLateFee

DECLARE @LateFeePart money SET @LateFeePart = dbo.WholeOrLess(@TranBalance,@LateFee)
IF @LateFeePart < 1 SET @LateFeePart = 0
SET @TranBalance = @TranBalance - @LateFeePart
DECLARE @AddEscrowLateFee money SET @AddEscrowLateFee = @LateFeePart - @LateFee

DECLARE @ToOwnedNSF money SET @ToOwnedNSF = dbo.WholeOrLess(@TranBalance,-@EscrowNSF)
SET @TranBalance = @TranBalance - @ToOwnedNSF

DECLARE @NSFPart money SET @NSFPart = dbo.WholeOrLess(@TranBalance,@NSF)
SET @TranBalance = @TranBalance - @NSFPart
DECLARE @AddEscrowNSF money SET @AddEscrowNSF = @NSFPart - @NSF

-------------------------------------------------------------
INSERT INTO @T
SELECT --@ToPrincipal,
@ToOwnedInterest ,@InterestPart ,@AddEscrowInterest,
@PrincipalPart ,
@ToOwnedLateFee ,@LateFeePart ,@AddEscrowLateFee,
@ToOwnedNSF ,@NSFPart , @AddEscrowNSF,
@TranBalance 
RETURN
END

--SELECT * FROM WaterFall (300,100,150,25,35,0,0,0)WF1
--CROSS APPLY WaterFall (300,500,170,25,35,WF1.AddEscrowInterest,WF1.AddEscrowLateFee,WF1.AddEscrowNSF)WF2




